#!/usr/bin/env python
#-*- coding:utf-8 -*-
__author__ = "Naich An (An@Naich.org)"
__copyright__ = "Copyright (c) 2011 Naich An" 

import MySQLdb
import datetime
import config

##connect to mysql server
#database = MySQLdb.connect(db='market', host='localhost', user='root', passwd='promise')
#cur = database.cursor()

##get the latest orderbook
##order_type : 0 -> user_abort, 1 -> sell, 2 -> buy, 3 -> done
#cur.execute("SELECT order_id,user_id,price,amount,time FROM orderbook WHERE order_type = 1 ORDER BY price ASC, time ASC")
#sellList = cur.fetchall()
#cur.execute("SELECT order_id,user_id,price,amount,time FROM orderbook WHERE order_type = 2 ORDER BY price ASC, time ASC")
#buyList = cur.fetchall()


def trade():
	#connect to mysql server
	cur = config.cur()

	#get the latest orderbook
	#order_type : 0 -> user_abort, 1 -> sell, 2 -> buy, 3 -> done
	cur.execute("SELECT order_id,user_id,price,amount,time FROM orderbook WHERE order_type = 1 ORDER BY price ASC, time ASC")
	sellList = cur.fetchall()
	cur.execute("SELECT order_id,user_id,price,amount,time FROM orderbook WHERE order_type = 2 ORDER BY price DESC, time ASC")
	buyList = cur.fetchall()
	
	#if the orderbook is empty, do nothing
	if  sellList != () and buyList != ():
		print 'Trade begins.'
	
		lowestSell = sellList[0]
		highestBuy = buyList[0]
		print lowestSell,highestBuy
		# if the highestBuy higher than lowestSell, the trade take action, else no trading.
		if highestBuy[2] >= lowestSell[2] :
		
			tradePrice = 0.0
			#the tradePrice should be the old one of lowestSell and highestBuy
			if lowestSell[4] > highestBuy[4]:
				tradePrice = highestBuy[2]
			else:
				tradePrice = lowestSell[2]
				
			#the tradeAmount should be the less one of lowestSell and highestBuy	
			tradeAmount = min(highestBuy[3], lowestSell[3])
			
			
			#write the price to Pricelist!
			cur.execute('INSERT INTO pricelist(price, amount) VALUES(%f, %f)' % (tradePrice, tradeAmount))
		
			
		
			#the less-amount one should be marked as 'done', and the more-amount's amount should change
			if highestBuy[3] > lowestSell[3]:
				cur.execute( 'UPDATE orderbook SET order_type = 3 WHERE order_id = %d '%lowestSell[0] )
				cur.execute( 'UPDATE orderbook SET amount = %f WHERE order_id = %d '% (highestBuy[3] - tradeAmount, highestBuy[0]) )
			
			else:
				if highestBuy[3] < lowestSell[3]:
					cur.execute( 'UPDATE orderbook SET order_type = 3 WHERE order_id = %d '%highestBuy[0] )
					cur.execute( 'UPDATE orderbook SET amount = %f WHERE order_id = %d '% (lowestSell[3] - tradeAmount, lowestSell[0]) )
				else:
				#if the two order have the same amount, mark both order_type -> 3
					cur.execute( 'UPDATE orderbook SET order_type = 3 WHERE order_id = %d '%highestBuy[0] )
					cur.execute( 'UPDATE orderbook SET order_type = 3 WHERE order_id = %d '%lowestSell[0] )
			
		
			#give the order owner money or stock
			cur.execute( 'UPDATE user SET stock = stock + %f WHERE user_id = %d'%(tradeAmount, highestBuy[1]) )
			cur.execute( 'UPDATE user SET money = money + %f WHERE user_id = %d'%(tradePrice * tradeAmount, lowestSell[1]) )
		
			#write the trade detail to trade_log
			cur.execute('INSERT INTO tradelog(order_id, user_id, amount, price) VALUES(%d, %d, %f, %f)'%(lowestSell[0], lowestSell[1], -1 * tradeAmount, tradePrice))
			cur.execute('INSERT INTO tradelog(order_id, user_id, amount, price) VALUES(%d, %d, %f, %f)'%(highestBuy[0], highestBuy[1], tradeAmount, tradePrice))
		
		
			#the end for this order ready for next order.
			print 'Trade done once!'
			trade()
	
		else:
			return 0
	else:
		return 1



#get the orderbook info to show to the users
def orderbook(length=5):
	
	def getFirstFive(orderList, length=5):
		#a sub-function to func "orderbook"
		listFive = []
		for price,amount in orderList:
			if len(listFive) <= length:
			#not too long to waste
				if listFive:
					if price != listFive[-1][0]:
						listFive += [[price,amount]]
					else:
						listFive[-1][1] += amount
				else:
					listFive += [[price, amount]]
		#if not long enough add someting instead			
		for i in range(0,length - len(listFive)):
				listFive += [[0,0]]
			
		#convert to strings
		for i in range(0, max(length, len(listFive))):
			if listFive[i] == [0,0]:
				listFive[i] = ['-','-']
			else:
				listFive[i] = ["%.2f"%listFive[i][0], int(listFive[i][1])]
		return listFive
		
	#connect to mysql server
	cur = config.cur()
	
	#get the latest orderbook
	#order_type : 0 -> user_abort, 1 -> sell, 2 -> buy, 3 -> done
	cur.execute("SELECT price,amount FROM orderbook WHERE order_type = 1 ORDER BY price ASC, time ASC")
	sellList = cur.fetchall()
	cur.execute("SELECT price,amount FROM orderbook WHERE order_type = 2 ORDER BY price DESC, time ASC")
	buyList = cur.fetchall()

	#the buy and sell Fives
	sellFive = getFirstFive(sellList,length)
	buyFive = getFirstFive(buyList,length)
		
	return [sellFive,buyFive]

def getinfo(user_id):
	#connect to mysql server
	cur = config.cur()
	
	#get my stock value and money in hold
	cur.execute('SELECT nick,money,stock FROM user WHERE user_id = %d'%user_id)
	(myNick,myMoney,myStock) = cur.fetchone()
			
	#get the information of my order now
	cur.execute('SELECT price,amount,order_type,order_id FROM orderbook WHERE user_id = %d AND (order_type = 1 OR order_type = 2)'%user_id)
	myorder = cur.fetchall()
			
	#get the lastest price, if no price is generated before, set it to 10
	cur.execute('SELECT price FROM pricelist ORDER BY time DESC LIMIT 1')
	try:
		priceNow = cur.fetchone()[0]
	except:
		priceNow = 10
	
	myWealth = myMoney + myStock * priceNow
	for order in myorder:
		#if it is a sell order, add the stock back
		if order[2] == 1:
			myWealth += order[1] * priceNow
		else:
			myWealth += order[0] * order[1]
			
	
	return (myMoney, myStock, myorder, myWealth, myNick)

#error code: 1->more than one order by this user; 2-> not enough money; 3-> not enough stock; 0-> all fine.		
def neworder(user_id, price, amount, order_type):
	#connect to mysql server
	cur = config.cur()
	
	if price <= 0 or amount <= 0 or int(amount) != amount or (int(price * 100 + 0.5) - price * 100 > 0.005):
		return '请检查您输入的数据'
	
	#get the user's stats
	(money,stock,orders) = getinfo(user_id)[:3]
	
	#if buy make sure this user has enough monty
	if order_type == 2:
		if price * amount > money:
			return '现金不足'
		else:
			#make sure no trade oneself
			for order in orders:
				if order[2] == 1 and price >= order[0] :
					return '您的买价高于您的某个卖单了，请重新输入'
			#make the change
			cur.execute('INSERT INTO orderbook(user_id, price, amount, order_type) VALUES(%d, %f, %f, %d)'%(user_id, price, amount, order_type))
			cur.execute('UPDATE user SET money = money - %f WHERE user_id = %d'%(price*amount, user_id))
			return '您的订单被接受.'
	
	#if sell make sure this user has enough stock
	if order_type == 1:
		if amount > stock:
			return '手中股票不足'
		else:
			#make sure no trade oneself
			for order in orders:
				if order[2] == 2 and price <= order[0] :
					return '您的卖价高于您的某个买单了，请重新输入'
			#make the change
			cur.execute('INSERT INTO orderbook(user_id, price, amount, order_type) VALUES(%d, %f, %f, %d)'%(user_id, price, amount, order_type))
			cur.execute('UPDATE user SET stock = stock - %f WHERE user_id = %d'%(amount, user_id))
			return '您的订单被接受.'
				

def abort(order_id, user_id):
	#connect to mysql server
	cur = config.cur()
	
	#get the order , make sure it is this user's own order
	cur.execute("SELECT price,amount,order_type FROM orderbook WHERE (order_id = %d AND user_id = %d AND (order_type = 1 OR order_type = 2))"%(order_id, user_id))
	order = cur.fetchone()
	if order:
		#set the order as 'aborted'
		order_type = order[2]
		cur.execute("UPDATE orderbook SET order_type = 0 WHERE order_id = %d"%order_id)
		#if it is a buy order, give the money back
		if order_type == 2:
			cur.execute("UPDATE user SET money = money + %f WHERE user_id = %d"%( (order[0]*order[1]), user_id) )
		#if it is a sell order, give the stock back
		if order_type == 1:
			cur.execute("UPDATE user SET stock = stock + %f WHERE user_id = %d"%( order[1], user_id) )
		return '操作成功，请检查您的账户余额。'
	else:
		return '参数错误，请重试。是否您重复操作了？'
	
def tradelog(user_id, loglength = 10):
	#connect to mysql server
	cur = config.cur()
	
	
	#get my trade log, max 10 lines
	cur.execute('SELECT price, amount, time FROM tradelog WHERE user_id = %d ORDER BY time DESC LIMIT %d' % (user_id, loglength))
	myTradelog = cur.fetchall()
	
	return myTradelog
	
def historyPriceNum(startTime=datetime.datetime(2011,11,16),timedelta=datetime.timedelta(0,3600)):
	#connect to mysql server
	cur = config.cur()
	
	
	t = startTime
	pricesList = []
	while t < datetime.datetime.now():
	
		cur.execute('SELECT price,amount FROM tradelog WHERE amount > 0 and (time between "%s" and "%s")'%(t.isoformat(' '), (t+1*timedelta).isoformat(' ')))
		pricesList += [[t,cur.fetchall()]]
		
		t = t + timedelta
	
	#before draw the price ,get the older price to start with
	try:
		cur.execute('SELECT price FROM tradelog WHERE amount > 0 and time < "%s" ORDER BY time DESC LIMIT 1'%(startTime.isoformat(' ')))
		originPrice = float(cur.fetchone()[0])
		historyPriceByHourNum = [[int(startTime.strftime('%s')), originPrice, 0]]
	except:
		historyPriceByHourNum = [[int(startTime.strftime('%s')), 0, 0]]
	
	
	for t, prices in pricesList:
		priceSum = 0
		amountSum = 0
		averagePrice = 0
		closePrice = 0
		if prices:
			priceSum = sum([ i[0] * i[1] for i in prices ])	
			amountSum += sum([i[1] for i in prices])
			averagePrice = priceSum/amountSum
			closePrice = prices[-1][0]
		else:
			averagePrice = historyPriceByHourNum[-1][1]
			closePrice = historyPriceByHourNum[-1][1]
			amountSum = 0
		historyPriceByHourNum += [[int(t.strftime('%s')), closePrice, priceSum]]
	
	return historyPriceByHourNum


























